package com.ybkj.daijia.serverUtils;

import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.util.WebUtils;

public class ExcelUtil {

    public static void export(HttpServletRequest req, HttpServletResponse response,
        String[] headers, List<List<String>> data) throws IOException {
        // 创建Excel的工作书册 Workbook,对应到一个excel文档
        XSSFWorkbook wb = new XSSFWorkbook();

        // 创建Excel的工作sheet,对应到一个excel文档的tab
        XSSFSheet sheet = wb.createSheet("sheet1");

        if (null != headers) {
            // 创建Excel的sheet的一行
            XSSFRow row = sheet.createRow(0);
            for (int i = 0; i < headers.length; i++) {
                // 创建一个Excel的单元格
                XSSFCell cell = row.createCell(i);
                cell.setCellValue(headers[i]);
            }
        }

//	    HSSFCellStyle cellStyle = wb.createCellStyle(); 
//	  	HSSFDataFormat format = wb.createDataFormat(); 
//	  	cellStyle.setDataFormat(format.getFormat("@")); 

        //數據
        for (int i = 1; i <= data.size(); i++) {
            XSSFRow drow;
            if (null == headers) {
                drow = sheet.createRow(i - 1);
            } else {
                drow = sheet.createRow(i);
            }
            for (int y = 0; y < data.get(i - 1).size(); y++) {
                // 创建一个Excel的单元格
                XSSFCell cell = drow.createCell(y);
                cell.setCellValue(data.get(i - 1).get(y));
            }
        }

        String bathPath = WebUtils.getRealPath(req.getSession().getServletContext(), "/");

        SimpleDateFormat monthFormat = new SimpleDateFormat("yyyyMM");
        Date now = new Date();
        String monthstr = monthFormat.format(now);

        String fileName = now.getTime() + ".xlsx";
        String filestr = "download/excel/" + monthstr + "/" + fileName;

        File f = new File(bathPath, filestr);

        if (!f.getParentFile().exists()) {
            f.getParentFile().mkdirs();
        }

        FileOutputStream os = new FileOutputStream(f);
        wb.write(os);
        os.close();

        //下載
        InputStream fis = new BufferedInputStream(new FileInputStream(f));
        byte[] buffer = new byte[fis.available()];
        fis.read(buffer);
        fis.close();
        // 清空response
        response.reset();
        // 设置response的Header
        response.addHeader("Content-Disposition", "attachment;filename=" + fileName);
        response.addHeader("Content-Length", "" + f.length());
        OutputStream toClient = new BufferedOutputStream(response.getOutputStream());
        response.setContentType("application/octet-stream");
        toClient.write(buffer);
        toClient.flush();
        toClient.close();

    }

    public static String createExcel(HttpServletRequest req, HttpServletResponse response,
        String[] headers, List<List<String>> data) throws IOException {
        // 创建Excel的工作书册 Workbook,对应到一个excel文档  
        XSSFWorkbook wb = new XSSFWorkbook();

        // 创建Excel的工作sheet,对应到一个excel文档的tab  
        XSSFSheet sheet = wb.createSheet("sheet1");

        if (null != headers) {
            // 创建Excel的sheet的一行
            XSSFRow row = sheet.createRow(0);
            for (int i = 0; i < headers.length; i++) {
                // 创建一个Excel的单元格  
                XSSFCell cell = row.createCell(i);
                cell.setCellValue(headers[i]);
            }
        }

        //  HSSFCellStyle cellStyle = wb.createCellStyle();
        //  HSSFDataFormat format = wb.createDataFormat();
        //  cellStyle.setDataFormat(format.getFormat("@"));

        //數據
        for (int i = 1; i <= data.size(); i++) {
            XSSFRow drow;
            if (null == headers) {
                drow = sheet.createRow(i - 1);
            } else {
                drow = sheet.createRow(i);
            }
            for (int y = 0; y < data.get(i - 1).size(); y++) {
                // 创建一个Excel的单元格  
                XSSFCell cell = drow.createCell(y);
                cell.setCellValue(data.get(i - 1).get(y));
                //          cell.setCellStyle(cellStyle);
            }
        }

        String bathPath = WebUtils.getRealPath(req.getSession().getServletContext(), "/");

        SimpleDateFormat monthFormat = new SimpleDateFormat("yyyyMM");
        Date now = new Date();
        String monthstr = monthFormat.format(now);

        String fileName = now.getTime() + ".xlsx";
        String filestr = "download/excel/" + monthstr + "/" + fileName;

        File f = new File(bathPath, filestr);

        if (!f.getParentFile().exists()) {
            f.getParentFile().mkdirs();
        }

        FileOutputStream os = new FileOutputStream(f);
        wb.write(os);
        os.close();

        return filestr;

    }


    public static String getStringCellValue(HSSFCell cell) {
        String strCell = "";
        switch (cell.getCellType()) {
            case HSSFCell.CELL_TYPE_STRING:
                strCell = cell.getStringCellValue();
                break;
            case HSSFCell.CELL_TYPE_NUMERIC:
                strCell = String.valueOf(cell.getNumericCellValue());
                break;
            case HSSFCell.CELL_TYPE_BOOLEAN:
                strCell = String.valueOf(cell.getBooleanCellValue());
                break;
            case HSSFCell.CELL_TYPE_BLANK:
                strCell = "";
                break;
            default:
                strCell = "";
                break;
        }
        if (strCell.equals("") || strCell == null) {
            return "";
        }
        if (cell == null) {
            return "";
        }
        return strCell;
    }

    /**
     * 获取单元格数据内容为日期类型的数据
     *
     * @param cell Excel单元格
     * @return String 单元格数据内容
     */
    public static String getDateCellValue(HSSFCell cell) {
        String result = "";
        try {
            int cellType = cell.getCellType();
            if (cellType == HSSFCell.CELL_TYPE_NUMERIC) {
                Date date = cell.getDateCellValue();
                result = (date.getYear() + 1900) + "-" + (date.getMonth() + 1)
                    + "-" + date.getDate();
            } else if (cellType == HSSFCell.CELL_TYPE_STRING) {
                String date = getStringCellValue(cell);
                result = date.replaceAll("[年月]", "-").replace("日", "").trim();
            } else if (cellType == HSSFCell.CELL_TYPE_BLANK) {
                result = "";
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return result;
    }

    /**
     * 根据HSSFCell类型设置数据
     *
     * @param cell
     * @return
     */
    public static String getCellFormatValue(Cell cell) {
        String cellvalue = "";
        if (cell != null) {
            // 判断当前Cell的Type
            switch (cell.getCellType()) {
                // 如果当前Cell的Type为NUMERIC
                case HSSFCell.CELL_TYPE_NUMERIC:
                case HSSFCell.CELL_TYPE_FORMULA: {
                    // 判断当前的cell是否为Date
                    if (HSSFDateUtil.isCellDateFormatted(cell)) {
                        // 如果是Date类型则，转化为Data格式

                        //方法1：这样子的data格式是带时分秒的：2011-10-12 0:00:00
                        //cellvalue = cell.getDateCellValue().toLocaleString();

                        //方法2：这样子的data格式是不带带时分秒的：2011-10-12
                        Date date = cell.getDateCellValue();
                        SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd");
                        cellvalue = sdf.format(date);

                    }
                    // 如果是纯数字
                    else {
                        // 取得当前Cell的数值
                        // cellvalue = String.valueOf(cell.getNumericCellValue());
                        cell.setCellType(Cell.CELL_TYPE_STRING);
                        cellvalue = cell.getStringCellValue();
                    }
                    break;
                }
                // 如果当前Cell的Type为STRIN
                case HSSFCell.CELL_TYPE_STRING:
                    // 取得当前的Cell字符串
                    cellvalue = cell.getRichStringCellValue().getString();
                    break;
                // 默认的Cell值
                default:
                    cellvalue = " ";
            }
        } else {
            cellvalue = "";
        }
        return cellvalue;

    }

    public static void export(String path, List<List<String>> data) throws IOException {
        // 创建Excel的工作书册 Workbook,对应到一个excel文档
        XSSFWorkbook wb = new XSSFWorkbook();

        // 创建Excel的工作sheet,对应到一个excel文档的tab
        XSSFSheet sheet = wb.createSheet("sheet1");
        if (null == data || 0 == data.size()) {
            return;
        }
        List<String> headers = data.get(0);
        if (null != headers) {
            // 创建Excel的sheet的一行
            XSSFRow row = sheet.createRow(0);
            for (int i = 0; i < headers.size(); i++) {
                // 创建一个Excel的单元格
                XSSFCell cell = row.createCell(i);
                cell.setCellValue(headers.get(i));
            }
        }

//	    HSSFCellStyle cellStyle = wb.createCellStyle();
//	  	HSSFDataFormat format = wb.createDataFormat();
//	  	cellStyle.setDataFormat(format.getFormat("@"));

        //數據
        if (null != data) {
            for (int i = 1; i < data.size(); i++) {
                XSSFRow drow;
                if (null == headers) {
                    drow = sheet.createRow(i - 1);
                } else {
                    drow = sheet.createRow(i);
                }
                for (int y = 0; y < data.get(i).size(); y++) {
                    // 创建一个Excel的单元格
                    XSSFCell cell = drow.createCell(y);
                    cell.setCellValue(data.get(i).get(y));
//	 	  	    cell.setCellStyle(cellStyle);
                }
            }
        }

        SimpleDateFormat monthFormat = new SimpleDateFormat("yyyyMM");
        Date now = new Date();
        String monthstr = monthFormat.format(now);

        String fileName = now.getTime() + ".xlsx";
        if (!path.endsWith("/") && !path.endsWith("\\")) {
            path += "/";
        }
        String filestr = path + fileName;

        File f = new File(filestr);

        if (!f.getParentFile().exists()) {
            f.getParentFile().mkdirs();
        }

        FileOutputStream os = new FileOutputStream(f);
        wb.write(os);
        os.close();
    }
}
